using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Firestarter.DB.Schema.BO;
using Firestarter.DB.Schema.Logic;
using FireStarterModeller.BO;
using Firestarter.DB.Schema.Test.Logic;
using FireStarterModeller.Test;

namespace Firestarter.DB.Schema.Test.Logic
{
    class SchemaTestPackBuilder
    {
        public static SchemaTestPack SetupDatabaseSchemaTestPack(TestUtilsDB utilsDb, IDbConnection connection, string databaseName)
        {
            using (connection)
            {
                SchemaTestPack testPack = new SchemaTestPack();

                testPack.Database = new DBDatabase();
                testPack.Database.DatabaseName = databaseName;

                testPack.ParentDBTable = new DBTable("ParentTable");
                testPack.Database.Tables.Add(testPack.ParentDBTable);
                DBTableBuilder parentDBTableBuilder = new DBTableBuilder(testPack.ParentDBTable);
                parentDBTableBuilder.AddPrimaryKeyColumn();
                parentDBTableBuilder.AddColumn("ParentName");

                testPack.ChildDBTable = new DBTable("ChildTable");
                testPack.Database.Tables.Add(testPack.ChildDBTable);
                DBTableBuilder childDBTableBuilder = new DBTableBuilder(testPack.ChildDBTable);
                childDBTableBuilder.AddPrimaryKeyColumn();
                childDBTableBuilder.AddColumn("ChildName");
                childDBTableBuilder.AddForeignKey("ForeignKeyName", testPack.ParentDBTable);

                testPack.UniqueKeyDBTable = new DBTable("UniqueKeyTable");
                testPack.Database.Tables.Add(testPack.UniqueKeyDBTable);
                DBTableBuilder indexedDBTableBuilder = new DBTableBuilder(testPack.UniqueKeyDBTable);
                indexedDBTableBuilder.AddPrimaryKeyColumn();
                indexedDBTableBuilder.AddUniqueKeyColumn("ExistingAKColumn");
                indexedDBTableBuilder.AddUniqueKey("CompositeUniqueKey", "KeyColumn1", "KeyColumn2");

                testPack.NoPrimaryKeyDBTable = new DBTable("NoPKTable");
                testPack.Database.Tables.Add(testPack.NoPrimaryKeyDBTable);
                DBTableBuilder noPKDBTableBuilder = new DBTableBuilder(testPack.NoPrimaryKeyDBTable);
                noPKDBTableBuilder.AddUniqueKey("UniqueKey", "Name");

                testPack.ManyDataTypesDBTable = new DBTable("ManyDataTypesTable");
                testPack.Database.Tables.Add(testPack.ManyDataTypesDBTable);
                DBTableBuilder manyDataTypesDBTableBuilder = new DBTableBuilder(testPack.ManyDataTypesDBTable);
                manyDataTypesDBTableBuilder.AddPrimaryKeyColumn();

                testPack.CompositePKDBTable = new DBTable("CompositePKTable");
                testPack.Database.Tables.Add(testPack.CompositePKDBTable);
                DBTableBuilder compositePKDBTableBuilder = new DBTableBuilder(testPack.CompositePKDBTable);
                compositePKDBTableBuilder.AddPrimaryKey("PKColumn1", "PKColumn2");
                compositePKDBTableBuilder.AddColumn("Name");

                testPack.CompositeFKDBTable = new DBTable("CompositeFKTable");
                testPack.Database.Tables.Add(testPack.CompositePKDBTable);
                DBTableBuilder compositeFKDBTableBuilder = new DBTableBuilder(testPack.CompositeFKDBTable);
                compositeFKDBTableBuilder.AddPrimaryKeyColumn();
                compositeFKDBTableBuilder.AddColumn("Name");
                compositeFKDBTableBuilder.AddForeignKey("CompositeForeignKey", testPack.CompositePKDBTable);

                CreateTableInDB(utilsDb, connection, testPack.ParentDBTable);
                CreateTableInDB(utilsDb, connection, testPack.ChildDBTable);
                CreateTableInDB(utilsDb, connection, testPack.UniqueKeyDBTable);
                CreateTableInDB(utilsDb, connection, testPack.NoPrimaryKeyDBTable);
                CreateTableInDB(utilsDb, connection, testPack.ManyDataTypesDBTable);
                CreateTableInDB(utilsDb, connection, testPack.CompositePKDBTable);
                CreateTableInDB(utilsDb, connection, testPack.CompositeFKDBTable);
                return testPack;
            }
        }

        private static void CreateTableInDB(TestUtilsDB utilsDb, IDbConnection connection, DBTable dbTable)
        {
            CreateTableSqlBuilder tableBuilderMySql = utilsDb.CreateSqlBuilderCreateTable(dbTable.TableName);
            DatabaseScriptCreator databaseScriptCreator = new DatabaseScriptCreator();
            string sql = databaseScriptCreator.CreateTable(dbTable, tableBuilderMySql);
            utilsDb.ExecuteSql(connection, sql);
        }
    }
}
